"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试
Case Name   : select into多表查询，多张表加锁，一张表加多个锁
Create At   : 2022/11/21
Owner       : opentestcase034
Description :
            1、设置并发更新同一行时单个锁的最长等待时间为3000
            2、创建表1表2,在表中插入数据
            3、会话1开启事务删除表数据
            4、select into多表查询，多张表加锁，一张表加多个锁
            5、会话1提交事务
            6、在表1表2中插入数据
            7、会话1开启事务select into多表查询，多张表加锁，一张表加多个锁
            8、在新会话删除表数据
            9、会话1提交事务
            10、在新会话删除表数据
            11、查询会话1新建表
            12、清理环境
Expect      :
            1、成功
            2、成功
            3、成功
            4、失败
            5、成功
            6、成功
            7、成功
            8、失败
            9、成功
            10、成功
            11、成功
            12、成功
History     :
    Modified by opentestcase034 2023/2/16: 优化用例清理环境正确恢复参数默认值
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class SQL(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.pri_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.db_path = macro.DB_INSTANCE_PATH
        self.t_name1 = 't_select_into_0093_1'
        self.t_name2 = 't_select_into_0093_2'
        self.t_name3 = 't_select_into_0093_3'
        self.config_lockwait = 'update_lockwait_timeout'

    def test_csncapsule_table(self):
        step1 = '--step1:设置并发更新同一行时单个锁的最长等待时间为3000;expect:成功--'
        self.log.info(step1)
        msg = self.primary_sh.execute_gsguc('reload',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                            f'{self.config_lockwait} = 3000')
        self.assertTrue(msg, '执行失败' + step1)

        step2 = '--step2:创建表1表2,在表中插入数据;expect:成功--'
        self.log.info(step2)
        sql_cmd = f'''
                drop table if exists {self.t_name1};
                create table {self.t_name1} (id int,c_clob varchar(10));
                drop table if exists {self.t_name2};
                create table {self.t_name2} (num int,c_clob1 varchar(10));
                insert into {self.t_name1} values(1,'a');
                insert into {self.t_name1} values(1,'b');
                insert into {self.t_name1} values(1,'c');
                insert into {self.t_name1} values(1,'d');
                insert into {self.t_name2} values(1,'a');
                insert into {self.t_name2} values(2,'f');
                insert into {self.t_name2} values(2,'g');
                insert into {self.t_name2} values(2,'h');
                  '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(2, result.count(self.Constant.TABLE_DROP_SUCCESS),
                         '执行失败' + step2)
        self.assertEqual(2, result.count(self.Constant.CREATE_TABLE_SUCCESS),
                         '执行失败' + step2)
        self.assertEqual(8, result.count(self.Constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step2)

        step3 = '--step3:会话1开启事务删除表数据;expect:成功--'
        self.log.info(step3)
        sql_cmd = f'''
                    begin;
                    delete from {self.t_name1};
                    delete from {self.t_name2};
                    select pg_sleep(20);
                    commit;
                  '''
        session1 = ComThread(self.primary_sh.execut_db_sql, args=(sql_cmd,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(5)

        step4 = '--step4:select into多表查询，多张表加锁，一张表加多个锁;expect:失败--'
        self.log.info(step4)
        sql_cmd = f"select * into {self.t_name3} " \
                  f"from {self.t_name1},{self.t_name2} " \
                  f"for update of {self.t_name1} nowait " \
                  f"for no key update of {self.t_name1} " \
                  f"for share of {self.t_name2};"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        exp = f'ERROR:  could not obtain lock ' \
              f'on row in relation "{self.t_name1}"'
        self.assertIn(exp, result, '执行失败' + step4)

        step5 = '--step5:会话1提交事务;expect:成功--'
        self.log.info(step5)
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.Constant.COMMIT_SUCCESS_MSG, session1_result,
                      '执行失败' + step5)

        step6 = '--step6:在表1表2中插入数据;expect:成功--'
        self.log.info(step6)
        sql_cmd = f'''
                    insert into {self.t_name1} values(1,'a');
                    insert into {self.t_name1} values(1,'b');
                    insert into {self.t_name1} values(1,'c');
                    insert into {self.t_name1} values(1,'d');
                    insert into {self.t_name2} values(1,'a');
                    insert into {self.t_name2} values(2,'f');
                    insert into {self.t_name2} values(2,'g');
                    insert into {self.t_name2} values(2,'h');
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(8, result.count(self.Constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step6)

        step7 = '--step7:会话1开启事务select into多表查询，' \
                '多张表加锁，一张表加多个锁;expect:成功--'
        self.log.info(step7)
        sql_cmd = f"begin;" \
                  f"select * into {self.t_name3} " \
                  f"from {self.t_name1},{self.t_name2} " \
                  f"for update of {self.t_name1} nowait " \
                  f"for no key update of {self.t_name1} " \
                  f"for share of {self.t_name2};" \
                  f"select pg_sleep(20);" \
                  f"commit;"
        session1 = ComThread(self.primary_sh.execut_db_sql, args=(sql_cmd,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(5)

        step8 = '--step8:新会话删除表数据;expect:失败--'
        self.log.info(step8)
        sql_cmd = f'''
                    delete from {self.t_name1};
                    delete from {self.t_name2};
                  '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        exp = 'ERROR:  Lock wait timeout'
        self.assertEqual(2, result.count(exp), '执行失败' + step8)

        step9 = '--step9:会话1提交事务;expect:成功--'
        self.log.info(step9)
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.Constant.COMMIT_SUCCESS_MSG, session1_result,
                      '执行失败' + step9)

        step10 = '--step10:新会话删除表数据;expect:成功--'
        self.log.info(step10)
        sql_cmd = f'''
                    delete from {self.t_name1};
                    delete from {self.t_name2};
                  '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(2, result.count(self.Constant.DELETE_SUCCESS_MSG),
                         '执行失败' + step10)

        step11 = '--step11:查询会话1新建表;expect:成功--'
        self.log.info(step11)
        sql_cmd = f'select * from {self.t_name3};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('16 rows', result, '执行失败' + step11)

    def tearDown(self):
        step12 = '--step12:清理环境;expect:成功--'
        self.log.info(step12)
        sql_cmd = f"drop table if exists {self.t_name1};" \
                  f"drop table if exists {self.t_name2};" \
                  f"drop table if exists {self.t_name3};"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(3, result.count(self.Constant.TABLE_DROP_SUCCESS),
                         '执行失败' + step12)
        msg = self.primary_sh.execute_gsguc('reload',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                        f'{self.config_lockwait} = 120000')
        self.assertTrue(msg, '执行失败' + step12)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')